package com.framewrok.core.poi.util.office;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public abstract class ExcelUtil {

	public static Workbook read(InputStream is, boolean oldExcel) throws IOException {
		Workbook xwb = null;
		if (oldExcel) {
			xwb = new HSSFWorkbook(is);
		} else {
			xwb = new XSSFWorkbook(is);
		}
		return xwb;
	}

	public static Workbook read(String path) throws IOException {
		InputStream is = null;
		try {
			File file = new File(path);
			is = new FileInputStream(file);
			Workbook xwb = null;
			if (xlsCheck(file)) {
				xwb = new HSSFWorkbook(is);
			} else {
				xwb = new XSSFWorkbook(is);
			}
			is.close();
			is = null;
			return xwb;
		} catch (IOException e) {
			throw new IOException("read file Fail" + path, e);
		} finally {
			if (is != null)
				try {
					is.close();
				} catch (IOException e) {
				}
		}

	}

	public static void write(Cell cell, Object obj) {
		if (cell == null || obj == null)
			return;
		if (obj instanceof Date) {
			cell.setCellValue(Date.class.cast(obj));
		} else if (obj instanceof Calendar) {
			cell.setCellValue(Calendar.class.cast(obj));
		} else if (obj instanceof String) {
			cell.setCellValue(String.class.cast(obj));
		} else if (obj instanceof RichTextString) {
			cell.setCellValue(RichTextString.class.cast(obj));
		} else if (obj instanceof Boolean) {
			cell.setCellValue(Boolean.class.cast(obj));
		} else if (obj instanceof Double) {
			cell.setCellValue(Double.class.cast(obj));
		} else {
			throw new IllegalArgumentException("not find match data type[" + obj.getClass().getName() + "] for excel please check it");
		}
	}

	/**
	 * 读单元格式文本值
	 * 
	 * @param cell
	 *            excel单元格
	 * @return
	 * @throws IOException
	 */
	public static String read(Cell cell) {
		// 用于返回结果
		String result = null;
		// 如果单元格为空，返回null
		if (cell == null) {
			return result;
		}
		// 判断单元格类型
		switch (cell.getCellType()) {
		case HSSFCell.CELL_TYPE_BLANK:
			result = "";
		case HSSFCell.CELL_TYPE_NUMERIC:// 数字类型
			// 处理日期格式、时间格式
			if (HSSFDateUtil.isCellDateFormatted(cell)) {
				SimpleDateFormat sdf = null;
				if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
					sdf = new SimpleDateFormat("HH:mm");
				} else {// 日期
					sdf = new SimpleDateFormat("yyyy-MM-dd");
				}
				Date date = cell.getDateCellValue();
				if (date == null)
					return "";
				result = sdf.format(date);
			} else if (cell.getCellStyle().getDataFormat() == 58) {
				// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
				Double value = cell.getNumericCellValue();
				Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
				result = sdf.format(date);
			} else {
				double value = cell.getNumericCellValue();
				CellStyle style = cell.getCellStyle();
				DecimalFormat format = new DecimalFormat();
				String temp = style.getDataFormatString();
				// 单元格设置成常规
				if (temp.equals("General")) {
					format.applyPattern("#");
				}
				result = format.format(value);
			}
			break;
		case HSSFCell.CELL_TYPE_STRING:// String类型
			result = cell.getStringCellValue();
			break;
		case HSSFCell.CELL_TYPE_FORMULA:// String类型
			break;
		case HSSFCell.CELL_TYPE_BOOLEAN:// String类型
			result = cell.getBooleanCellValue() + "";
			break;
		case HSSFCell.CELL_TYPE_ERROR:// String类型
			result = cell.getStringCellValue();
			break;
		default:
			throw new IllegalArgumentException(cell.getRow().getRowNum() + "-" + cell.getColumnIndex() + ":" + cell.getCellType() + "read fail.");
		}
		return result;
	}

	/**
	 * excel文件检查<br/>
	 * excel版本是2013返回true<br/>
	 * excel版本是2017及以后返回false<br/>
	 * 
	 * @param file
	 *            读取文件
	 * @return
	 * @throws IOException
	 */
	private static boolean xlsCheck(File file) throws IOException {
		InputStream is = null;
		boolean rst = true;
		try {
			is = new FileInputStream(file);
			byte[] bt = new byte[10];
			is.read(bt);
			StringBuilder sb = new StringBuilder();
			for (int i = 0; i < bt.length; i++) {
				sb.append(byteToHex(bt[i]));
			}
			if ("D0CF11E0A1B11AE10000".equals(sb.toString())) {
				// word,excel 2013
				rst = true;
			} else if ("504B0304140000000800".equals(sb.toString())) {
				// word,excel,2017以后
				rst = false;
			} else {
				throw new IOException("[" + file.getAbsolutePath() + "] is not excel.");
			}
		} catch (FileNotFoundException e) {
			throw new IOException("read file Fail[" + (file == null ? "" : file.getAbsolutePath() + "]"), e);
		} finally {
			if (is != null)
				try {
					is.close();
				} catch (IOException e) {
				}
		}
		return rst;
	}

	/**
	 * byt转换成十六进制字符串
	 * 
	 * @param b
	 * @return
	 */
	private static String byteToHex(byte b) {
		String hex = Integer.toHexString(b & 0xFF);
		if (hex.length() < 2) {
			hex = "0" + hex;
		}
		return hex.toUpperCase();
	}

}
